import mysql
import requests
import time
import random
import json
from typing import Dict, Union
from mysql.connector import Error

from applications import BaseConfig
from crawler.region_codes import PROVINCE_CODES


# 受灾面积
class AffectedAreaYieldCrawler:
    def __init__(self):
        self.base_url = "https://data.stats.gov.cn/easyquery.htm"
        self.zb_mapping = {
            "A0D1801": "受灾面积(千公顷)",
            "A0D1802": "水灾受灾面积(千公顷)",
            "A0D1803": "早灾受灾面积(千公顷)",
            "A0D1804": "风雹灾受灾面积(千公顷)",
            "A0D1805": "冷冻灾受灾面积(千公顷)",
            "A0D1806": "成灾面积(千公顷)",
            "A0D1807": "水灾成灾面积(千公顷)",
            "A0D1808": "旱灾成灾面积(千公顷)",
            "A0D1809": "风雹灾成灾面积(千公顷)",
            "A0D180A": "冷冻灾成灾面积(千公顷)",
        }

    def _generate_headers(self) -> Dict:
        """生成随机请求头"""
        return {
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
            "Sec-Fetch-Dest": "document",
            "Sec-Fetch-Mode": "navigate",
            "Sec-Fetch-Site": "none",
            "Sec-Fetch-User": "?1",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Mobile Safari/537.36",
            "sec-ch-ua": "\"Chromium\";v=\"134\", \"Not:A-Brand\";v=\"24\", \"Google Chrome\";v=\"134\"",
            "sec-ch-ua-mobile": "?1",
            "sec-ch-ua-platform": "\"Android\"",
        }

    def _parse_years(self, years: Union[int, str]) -> str:

        """解析年份参数"""
        if isinstance(years, int):
            current_year = time.localtime().tm_year
            return f"{current_year - years}-{current_year}"
        return str(years)

    def get_data(self, region_code: str, years: Union[int, str] = 5) -> Dict:
        cookies = {
            "_trs_uv": "m7z58rv4_6267_ie9j",
            "JSESSIONID": "yyCyWhIFvtp3edFo7Rh-SHj_NdCajpa76cFO5O3W_wF2DKBCaeUP!1948715794",
            "u": "2",
            "wzws_sessionid": "gmZjNWVlMYFlNjViYTWAMTExLjg1LjIxNy4yN6Bn28Ag"
        }

        if not (len(region_code) == 6 and region_code.isdigit()):
            raise ValueError("行政区代码应为6位数字")

        params = {
            "m": "QueryData",
            "dbcode": "fsnd",
            "rowcode": "zb",
            "colcode": "sj",
            "wds": json.dumps([{"wdcode": "reg", "valuecode": region_code}]),
            "dfwds": json.dumps([
                {"wdcode": "zb", "valuecode": "A0D18"},  # 固定农业指标
                {"wdcode": "sj", "valuecode": "LAST20"}
            ])
        }
        try:
            response = requests.get(
                self.base_url,
                headers=self._generate_headers(),
                cookies=cookies,
                params=params,
                timeout=10
            )
            response.raise_for_status()
            return self._parse_response(response.json())

        except Exception as e:
            return {"error": str(e)}

    def _parse_response(self, data: Dict) -> Dict:
        """解析API响应"""
        result = {name: {} for name in self.zb_mapping.values()}

        for node in data.get("returndata", {}).get("datanodes", []):

            # 解析指标代码和年份
            code_parts = node["code"].split(".")
            zb_code = code_parts[1].split("_")[0]
            year = code_parts[-1]

            if indicator := self.zb_mapping.get(zb_code):
                result[indicator][year] = node["data"]["strdata"]

        return result


class DatabaseManager:
    def __init__(self, config):
        self.config = config
        self.create_table()

    def create_connection(self):
        """创建数据库连接"""
        try:
            return mysql.connector.connect(**self.config)
        except Error as e:
            print(f"数据库连接失败: {e}")
            return None

    def create_table(self):
        """创建数据表"""
        create_table_query = """
        CREATE TABLE IF NOT EXISTS affected_area (
            id INT AUTO_INCREMENT PRIMARY KEY,
            province_code VARCHAR(6) NOT NULL,
            province_name VARCHAR(20) NOT NULL,
            year VARCHAR(4) NOT NULL,
            indicator VARCHAR(50) NOT NULL,
            value FLOAT,
            update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_record (province_code, year, indicator)
        )
        """
        conn = self.create_connection()
        if conn:
            try:
                cursor = conn.cursor()
                cursor.execute(create_table_query)
                conn.commit()
            except Error as e:
                print(f"创建表失败: {e}")
            finally:
                if conn.is_connected():
                    cursor.close()
                    conn.close()

    def save_data(self, province_code, province_name, data):
        """保存数据到数据库"""
        insert_query = """
        INSERT INTO affected_area 
        (province_code, province_name, year, indicator, value)
        VALUES (%s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
        value = VALUES(value),
        update_time = CURRENT_TIMESTAMP
        """

        conn = self.create_connection()
        if not conn:
            return

        try:
            cursor = conn.cursor()
            records = []

            for indicator, years_data in data.items():
                for year, value in years_data.items():
                    try:
                        num_value = float(value) if value else None
                        records.append((
                            province_code,
                            province_name,
                            year,
                            indicator,
                            num_value
                        ))
                    except ValueError:
                        continue

            # 批量插入数据
            if records:
                cursor.executemany(insert_query, records)
                conn.commit()
                print(f"已保存 {len(records)} 条数据: {province_name}")

        except Error as e:
            print(f"数据保存失败: {e}")
            conn.rollback()
        finally:
            if conn.is_connected():
                cursor.close()
                conn.close()


def main():
    # 数据库配置
    db_config = {
        'host': BaseConfig.DB_HOST,
        'user': BaseConfig.DB_USER,
        'password': BaseConfig.DB_PASSWORD,
        'database': BaseConfig.DB_NAME,
        'port': BaseConfig.DB_PORT
    }

    # 初始化组件
    crawler = AffectedAreaYieldCrawler()
    db_manager = DatabaseManager(db_config)

    # 遍历所有省份
    for province_name, province_code in PROVINCE_CODES.items():
        print(f"正在获取数据: {province_name}")

        try:
            # 获取数据（这里获取最近20年数据）
            data = crawler.get_data(province_code, 20)

            # 过滤空数据
            if not data or 'error' in data:
                print(f"数据获取失败: {province_name}")
                continue
            # 保存到数据库
            db_manager.save_data(province_code, province_name, data)
            # 添加随机延时防止被封（3-8秒）
            time.sleep(random.uniform(3, 8))
        except Exception as e:
            print(f"处理 {province_name} 时发生异常: {str(e)}")
            continue


if __name__ == "__main__":
    main()
